package cn.telling.telMarketCentre.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

import cn.telling.common.util.TellingConstance;
import cn.telling.product.vo.ShopProductVo;
import cn.telling.telMarketCentre.dao.ITelMarketSupplyDao;
import cn.telling.utils.BaseUtil;
import cn.telling.utils.StringHelperTools;
/**
 * @Description: 通讯市场处理Dao
 * @author liugeng
 * @version V1.0
 *
 */
@Repository("telMarketSupplyDao")
public class TelMarketSupplyDaoImpl implements ITelMarketSupplyDao {
	private static Logger logger = Logger.getLogger(TelMarketSupplyDaoImpl.class);
	
	@Autowired
	@Qualifier("film-template")
	private JdbcTemplate jdbcTemplate;
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	/**
	 * 加载通讯市场产品列表
	 */
	@Override
	public List<Map<String, String>> loadTelMarketProductList(String choose) {
		String sql = "select pm.productId,pm.productName, pb.brandName " +
					 " from product_main pm,product_brand pb " +
					 " where pb.brandId = pm.brandId " + 
					 " and pm.saleType = '" + TellingConstance.SALETYPE_2 + "' " + 
					 " and pm.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
					 " and pm.publish = '1' " +
					 " and pm.productname like '%" + choose + "%'" +
					 " order by pb.brandName, pm.productName ";
		logger.debug("查询通讯市场类产品列表:" + sql);
		List<Map<String, String>> list = jdbcTemplate.query(sql, rowMapper);
		return list;
	}
	
	private ParameterizedRowMapper<Map<String, String>> rowMapper = new ParameterizedRowMapper<Map<String,String>>() {
		@Override
		public Map<String, String> mapRow(ResultSet rs, int rowNum)
				throws SQLException {
			Map<String, String> map = new HashMap<String, String>();
			map.put("productId", StringHelperTools.nvl(rs.getString("productId")));
			map.put("productName", StringHelperTools.nvl(rs.getString("productName")));
			map.put("brandName", StringHelperTools.nvl(rs.getString("brandName")));
			map.put("checked", "0");// 默认未选中
			map.put("topFlag", "0");//默认不在top值范围内
			return map;
		}
	};
	
	/**
	 * 查询通讯市场卖家  产品已上架、已下架产品
	 */
	@Override
	public List<ShopProductVo> getShopProductListNoPage(String userId) {
		String sql = "select sp.id, sp.productId,sp.shopId,sp.supplyId,pm.productName,pp.picturePath1,max(sa.saleType) as saleMode " +
					 " from shop_product sp " +
					 " inner join product_main pm on sp.productId = pm.productId and pm.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
					 " left  join product_pic_relation ppr on pm.productId = ppr.product_id " + 
					 " left  join product_picture pp on ppr.pictureId = pp.pictureId and pp.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
					 " inner join supply s on sp.supplyId = s.id and s.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
					 " inner join supply_area0 sa on sp.id = sa.supplyProductId and sa.del_flag = '" + TellingConstance.DEL_FLAG_1+"' and sa.isValId = '" + TellingConstance.IS_VALID_01 + "' " +
					 " where s.user_id = '" + userId + "' and sa.saleStatus <> '1' " +
					 " and sp.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sp.isValId = '" + TellingConstance.IS_VALID_01 + "' and pm.saleType = '" + TellingConstance.SALETYPE_2 + "' " +
					 " group by sp.id, sp.productId,sp.shopId,sp.supplyId,pm.productName,pp.picturePath1 " +
					 " order by sp.id desc ";
		return jdbcTemplate.query(sql, tempMapper);
	}
	
	private ParameterizedRowMapper<ShopProductVo> tempMapper = new ParameterizedRowMapper<ShopProductVo>() {
		@Override
		public ShopProductVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			ShopProductVo shopProductVo = new ShopProductVo();
			shopProductVo.setId(rs.getBigDecimal("id"));
			shopProductVo.setProductId(rs.getBigDecimal("productId"));
			shopProductVo.setShopId(rs.getString("shopId"));
			shopProductVo.setSupplyId(rs.getBigDecimal("supplyId"));
			shopProductVo.setProductName(rs.getString("productName"));
			shopProductVo.setProductPic(rs.getString("picturePath1"));
			shopProductVo.setSaleMode(rs.getString("saleMode"));
			return shopProductVo;
		}
	};
	/**
	 * 发布通讯市场类产品
	 */
	@Override
	public int insertTelMarketShopProduct(String productId,String supplyId,String shopId) {
		
		String sql = "select id from shop_product where productId = ? and supplyId = ? ";
		Object obj[] = new Object[]{productId,supplyId};
		obj = StringHelperTools.nullToString(obj);
		
		List<Integer> idList = jdbcTemplate.query(sql,obj,new ParameterizedRowMapper<Integer>(){
			@Override
			public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getInt("id");
			}
			
		});
		
		int shopProductId = 0;
		if(idList != null && idList.size() > 0){
			shopProductId = idList.get(0);
			// 更新shop_product数据
			String sqlUpdate = "update shop_product set del_flag = ? where id = ? ";
			Object param[] = new Object[]{TellingConstance.DEL_FLAG_1,shopProductId };
			param = StringHelperTools.nullToString(param);
			
			logger.debug("更新shop_product数据:"+BaseUtil.logSQL(sqlUpdate,param));
			jdbcTemplate.update(sqlUpdate, param);
		}else {
			String sqlSEQ ="select SEQ_SHOP_PRODUCT_KEY.NEXTVAL from dual";
			List<String> list = jdbcTemplate.query(sqlSEQ, new ParameterizedRowMapper<String>(){
				@Override
				public String mapRow(ResultSet rs, int rowNum) throws SQLException {
					return rs.getString("NEXTVAL");
				}});
			
			if(list != null && list.size() > 0){
				shopProductId = Integer.parseInt(list.get(0));
			}
			
			String sqlInsert = "insert into shop_product (id,productId,supplyId,shopId,saleMode,isFeng,isValid,del_flag) " +
					" values(?,?,?,?,?,?,?,?) ";
			Object[] objects = new Object[]{shopProductId,Integer.parseInt(productId),
										supplyId,shopId,
										TellingConstance.TELMARKETMODE,"02",
										TellingConstance.IS_VALID_01,TellingConstance.DEL_FLAG_1
										};
			objects = StringHelperTools.nullToString(objects);
			logger.debug("发布通讯市场产品shop_product:" + BaseUtil.logSQL(sqlInsert,objects));
			jdbcTemplate.update(sqlInsert, objects);
		}
		
		return shopProductId;
	}
	
	@Override
	public int insertTelMarketProduct(String shopProductId,String price, String stockNum,String gifts,String aftersale,String userId) {
		// 删除 supply_area0 数据
		String sqlDel = "update supply_area0 set del_flag = ?, update_user = ? where supplyproductid = ? ";
		Object obj[] = new Object[]{TellingConstance.DEL_FLAG_0, userId, shopProductId};
		obj = StringHelperTools.nullToString(obj);
		
		logger.debug("删除supply_area0数据:"+BaseUtil.logSQL(sqlDel,obj));
		
		jdbcTemplate.update(sqlDel, obj);
		
		String sqlSEQ ="select SEQ_SUPPLY_AREA0_PK.NEXTVAL from dual";
		
		List<String> list = jdbcTemplate.query(sqlSEQ, new ParameterizedRowMapper<String>(){
			@Override
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getString("NEXTVAL");
			}});
		
		String supplyArea0Id = "0";
		if(list != null && list.size() > 0){
			supplyArea0Id = list.get(0);
		}
		
		String insertSql = "insert into supply_area0 (id,supplyproductid,areaId,priceretailonline,stocknumber,overplusNumber,gifts,aftersale,saleType,status,salestatus,isvalid,del_flag,createTime,onShelfTime,update_user) " + 
						   "values (?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate,sysdate,?) ";
		Object[] objects = new Object[]{Integer.parseInt(supplyArea0Id),Integer.parseInt(shopProductId),Integer.parseInt("2"),
										Double.parseDouble(price),stockNum,stockNum,gifts,aftersale,
										TellingConstance.SALETYPE2,TellingConstance.STATUS_1,TellingConstance.SALESTATUS3,
										TellingConstance.IS_VALID_01,TellingConstance.DEL_FLAG_1,userId
									};
		objects = StringHelperTools.nullToString(objects);
		logger.debug("发布通讯市场产品supply_area0:" + BaseUtil.logSQL(insertSql,objects));
		
		jdbcTemplate.update(insertSql, objects);
		return Integer.parseInt(supplyArea0Id);
	}
	
	/**获得通讯市场上月销量top N 的产品*/
	@Override
	public List<String> getTelMarketProductTop(int topValue) {
		String sql = "select productId from ( " +
						" select tss.productId, nvl(sum(tss.saleQty),0) product_sell_num " + 
						" from telmarket_sale_statics tss " + 
						" where tss.statics_date < trunc(sysdate, 'mm') " + 
						" and tss.statics_date >= trunc(add_months(sysdate, -1), 'mm') " +
						" group by tss.productId " + 
						" order by product_sell_num desc " + 
					 " ) temp " + 
					 " where rownum <= ? ";
		Object params[] = new Object[]{topValue};
		params = StringHelperTools.nullToString(params);
		
		logger.debug(String.format("查询通讯市场月销量top%d:%s", topValue,BaseUtil.logSQL(sql, params)));
		
		List<String> productIdList = jdbcTemplate.query(sql, params, new ParameterizedRowMapper<String>(){
			@Override
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getString("productId");
			}});
		return productIdList;
	}
}
